package com.wingsoft.homeFlow.utils;

import cn.hutool.db.DbUtil;
import cn.hutool.json.JSONUtil;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidPooledConnection;

import com.wingsoft.common.constant.Constants;
import com.wingsoft.common.exception.ServiceException;
import com.wingsoft.common.utils.DateUtil;
import com.wingsoft.homeFlow.bo.HomeFlowSourcePoolBo;
import com.wingsoft.homeFlow.entity.HomeFlowSource;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.ObjectUtils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;
import java.util.regex.Pattern;


@Slf4j
public class DbUtils {
    private static final ConcurrentHashMap<String, HomeFlowSourcePoolBo> dataSourceMap = new ConcurrentHashMap<>();

    public static final String SPLIT_CHAR = "Φ";

    // 定义一些常见的SQL注入关键词和模式
    private static final Pattern SQL_INJECTION_PATTERN = Pattern.compile(
            "('(''|[^'])*')|(\\b(union|select|insert|delete|update|drop|alter|truncate|grant|execute|exec|xp_cmdshell|call|declare|source|use|describe|show)\\b|;|--|#|/\\*|\\*/)",
            Pattern.CASE_INSENSITIVE
    );

    public static void containsSqlInjection(Object input) {
        if (Objects.isNull(input)) {
            return;
        }
        if (SQL_INJECTION_PATTERN.matcher(String.valueOf(input)).find()) {
            throw new ServiceException("输入包含SQL注入风险！参数值为：" + input);
        }
    }

    private static DruidDataSource getDataSource(HomeFlowSource homeFlowSource) {
        if (Objects.isNull(homeFlowSource)) {
            throw new ServiceException("数据库连接信息为空");
        }
        HomeFlowSourcePoolBo homeFlowSourcePoolBo = dataSourceMap.get(new HomeFlowSourcePoolBo(homeFlowSource).getKey());
        if (Objects.nonNull(homeFlowSourcePoolBo)) {
            if (JSONUtil.toJsonStr(homeFlowSourcePoolBo.getHomeFlowSource()).equals(JSONUtil.toJsonStr(homeFlowSource))) {
                homeFlowSourcePoolBo.setLastActiveTime(new Date());
                dataSourceMap.put(homeFlowSourcePoolBo.getKey(), homeFlowSourcePoolBo);
                return homeFlowSourcePoolBo.getDruidDataSource();
            } else {
                DbUtil.close(homeFlowSourcePoolBo.getDruidDataSource());
            }
        }
        DruidDataSource dataSource = new DruidDataSource();
        // 设置数据库连接信息
        if (ObjectUtils.isNotEmpty(homeFlowSource.getDriver())) {
            dataSource.setDriverClassName(homeFlowSource.getDriver());
        }
        dataSource.setUrl(homeFlowSource.getAddress());
        dataSource.setUsername(homeFlowSource.getUsername());
        dataSource.setPassword(homeFlowSource.getPassword());
        dataSource.setConnectionErrorRetryAttempts(0);
        dataSource.setBreakAfterAcquireFailure(true);
        // 配置连接池参数
        dataSource.setInitialSize(5); // 初始化连接数
        dataSource.setMaxActive(20);  // 最大连接数
        dataSource.setMinIdle(5);    // 最小空闲连接数
        dataSource.setRemoveAbandoned(true);
        dataSource.setRemoveAbandonedTimeout(600); // 超过600秒未使用的连接将被回收
        dataSource.setQueryTimeout(600); // 查询超时时间
        // 将数据源添加到缓存中
        DruidPooledConnection connection = null;
        try {
            connection = dataSource.getConnection();
            homeFlowSourcePoolBo = new HomeFlowSourcePoolBo(homeFlowSource);
            homeFlowSourcePoolBo.setDruidDataSource(dataSource);
            homeFlowSourcePoolBo.setLastActiveTime(new Date());
            dataSourceMap.put(homeFlowSourcePoolBo.getKey(), homeFlowSourcePoolBo);
        } catch (Exception e) {
            DbUtil.close(dataSource);
            log.error("数据库连接失败", e);
            throw new ServiceException("数据库连接失败");
        } finally {
            DbUtil.close(connection);
        }
        return dataSource;
    }

    public static Connection getSingleConnection(HomeFlowSource homeFlowSource) {
        try {
            return DriverManager.getConnection(homeFlowSource.getAddress(), homeFlowSource.getUsername(), homeFlowSource.getPassword());
        } catch (Exception e) {
            log.error("获取数据库连接失败", e);
            throw new ServiceException("获取数据库连接失败");
        }
    }

    public static DruidPooledConnection getConnection(HomeFlowSource homeFlowSource) {
        try {
            DruidDataSource dataSource = getDataSource(homeFlowSource);
            return dataSource.getConnection();
        } catch (Exception e) {
            log.error("获取数据库连接失败", e);
            removeDataSource(homeFlowSource);
            throw new ServiceException("获取数据库连接失败");
        }
    }

    public static void removeDataSource(HomeFlowSource homeFlowSource) {
        HomeFlowSourcePoolBo homeFlowSourcePoolBo = dataSourceMap.get(new HomeFlowSourcePoolBo(homeFlowSource).getKey());
        if (Objects.nonNull(homeFlowSourcePoolBo)) {
            DbUtil.close(homeFlowSourcePoolBo.getDruidDataSource());
            dataSourceMap.remove(homeFlowSourcePoolBo.getKey());
        }
    }

    public static void cleanUnusedDataSource() {
        for (Map.Entry<String, HomeFlowSourcePoolBo> entry : dataSourceMap.entrySet()) {
            Date lastActiveTime = entry.getValue().getLastActiveTime();
            if (DateUtil.addDay(new Date(), -7).after(lastActiveTime)) {
                DbUtil.close(entry.getValue().getDruidDataSource());
                dataSourceMap.remove(entry.getKey());
            }
        }
    }

    public static <T> List<List<T>> listChunk(List<T> list) {
        return listChunk(list, Constants.SQL_CONDITION_IN_MAX_NUM);
    }

    public static <T> List<List<T>> listChunk(List<T> list, int size) {
        List<List<T>> chunks = new ArrayList<>();
        for (int i = 0; i < list.size(); i += size) {
            int end = Math.min(i + size, list.size());
            chunks.add(list.subList(i, end));
        }
        return chunks;
    }

}
